package gov.nhso.prov.acc.apps.acc6;

import java.io.OutputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.isud.core.ISUD;
import com.isud.core.ISUDFactory;

import gov.nhso.prov.acc.beans.Acc67SuspendBean;
import gov.nhso.prov.acc.core.DBConnection;
import gov.nhso.prov.acc.utils.Utility;

@SuppressWarnings({"rawtypes","unchecked","deprecation"})
public class ACC60108 extends Utility{
	public String url = "/apps/acc6/ACC60108A.jsp";
	
	public ACC60108(HttpServletRequest request, HttpServletResponse response) {
		this.message(request).clearMsg();
		String job = this.getAttr(request, "job");
		if(job.equals("init")){
			this.init(request);
		} else if (job.equals("save")) {
			this.save(request);
		} else if (job.equals("edit")) {
			this.edit(request);
		} else if (job.equals("search")) {
			this.search(request);
		} else if (job.equals("confirm")) {
			this.confirm(request);
		} else if (job.equals("searchByDate")) {
			this.searchByDate(request);
		} else if (job.equals("export")) {
			this.export(request, response);
		} else if(job.equals("downloadTemplate")){
			this.downloadTemplate(request, response);
		} else if(job.equals("upload")){
			this.upload(request);
		} else if(job.equals("back")){
			this.init(request);
		}
	}
	
	private String init(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			request.setAttribute("lsProvince", this.dataUtil(dao).lsProvince());
			request.setAttribute("lsNhsoZone", this.dataUtil(dao).lsNhsoZone());
			request.setAttribute("areaLevel", this.properties().areaLevel());
			request.setAttribute("job", "searchByDate");
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				this.url = "/apps/acc6/ACC60108B.jsp";
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	public String searchByDate(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			List ls = new ArrayList();
			String result = "";
			List listData = new ArrayList();
			String suspendDate = this.getAttr(request, "suspendDate");
			String areaLevel = this.getAttr(request, "areaLevel");
			String nhsoZone = this.getAttr(request, "nhsoZone");
			String provinceId = this.getAttr(request, "province");
			String condition = "";
			String[] arrStr = suspendDate.split("\\/");
			String mm = arrStr[1];
			String yyyy = arrStr[2];
			StringBuffer sb = new StringBuffer();
			double expenseAmt = 0;
			double incomeAnt = 0;
			boolean isConfirm = false;
			int count = 0;
			Map map = new HashMap();
			ls = this.dataUtil(dao).lsProvince();
			Acc67SuspendBean asb = new Acc67SuspendBean();
			asb.setMm(mm);
			asb.setYyyy(yyyy);
			
			if (areaLevel != null) {
				if (areaLevel.equals("2")) {
					condition = " AND PROVINCE_ID IN (SELECT PROVINCE_ID FROM PROVINCE WHERE NHSO_ZONE = '" + nhsoZone + "' )";
					ls = this.dataUtil(dao).lsProvinceByZone(nhsoZone);
				} else if (areaLevel.equals("3")) {
					asb.setProvinceId(provinceId);
					ls = this.dataUtil(dao).lsProvinceById(provinceId);
				}
			} 
			
			listData = dao.selectList(asb, condition + " ORDER BY PROVINCE_ID ASC");
			request.setAttribute("suspendDate", suspendDate);
			if (listData.size() <= 0) {
				for (int i=0;i<ls.size();i++) {
					map = (Map)ls.get(i);
					asb = new Acc67SuspendBean();
					sb.delete(0, sb.length());
					sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) AS SUM_ACC6_AMOUNT ");
					sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
					sb.append("AND INCOME_DATE <= TO_DATE('"+this.dateUtil().changeFormatDate(suspendDate, "/","")+"235959','DDMMYYYYHH24MISS') ");
					incomeAnt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
					sb.delete(0, sb.length());
					sb.append("SELECT (NVL(SUM(EXPENSE_AMOUNT), 0)+NVL(SUM(TAX_VALUE), 0)) AS SUM_ACC6_AMOUNT ");
					sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
					sb.append("AND EXPENSE_DATE  <= TO_DATE('"+this.dateUtil().changeFormatDate(suspendDate, "/","")+"235959','DDMMYYYYHH24MISS') ");
					expenseAmt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
					result = String.valueOf(incomeAnt - expenseAmt);
					asb.setProvinceId(map.get("PROVINCE_ID").toString());
					asb.setProvinceName(map.get("PROVINCE_NAME").toString());
					asb.setAcc67Amount(result);
					listData.add(asb);
				}
				request.setAttribute("job", "save");
			} else {
				listData = new ArrayList();
				for (int i=0;i<ls.size();i++) {
					map = (Map)ls.get(i);
					asb = new Acc67SuspendBean();
					asb.setProvinceId(dao.chkData(map.get("PROVINCE_ID")));
					asb.setMm(mm);
					asb.setYyyy(yyyy);
					dao.select(asb);
					if (asb.getAcc67Amount() == null || asb.getAcc67Amount().equals("")) {
						sb.delete(0, sb.length());
						sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) AS SUM_ACC6_AMOUNT ");
						sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
						sb.append("AND INCOME_DATE <= TO_DATE('"+this.dateUtil().changeFormatDate(suspendDate, "/","")+"235959','DDMMYYYYHH24MISS') ");
						incomeAnt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
						sb.delete(0, sb.length());
						sb.append("SELECT (NVL(SUM(EXPENSE_AMOUNT), 0)+NVL(SUM(TAX_VALUE), 0)) AS SUM_ACC6_AMOUNT ");
						sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
						sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
						sb.append("AND EXPENSE_DATE  <= TO_DATE('"+this.dateUtil().changeFormatDate(suspendDate, "/","")+"235959','DDMMYYYYHH24MISS') ");
						expenseAmt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
						result = String.valueOf(incomeAnt - expenseAmt);
						asb.setProvinceId(map.get("PROVINCE_ID").toString());
						asb.setProvinceName(map.get("PROVINCE_NAME").toString());
						asb.setAcc67Amount(result);
						listData.add(asb);
					} else {
						asb.setProvinceName(map.get("PROVINCE_NAME").toString());
						if (asb.getSuspendStatus().equals("Y")) {
							request.setAttribute("suspendDate", this.dateUtil().changeDateFromDB(asb.getSuspendDate(), "/"));
							count++;
						}
						listData.add(asb);
					}
				}
				/*for (int i=0;i<listData.size();i++) {
					Acc67SuspendBean bean = (Acc67SuspendBean)listData.get(i);
					bean.setProvinceName(this.dataUtil(dao).getProvinceName(bean.getProvinceId()));
					if (bean.getSuspendStatus().equals("Y")) {
						request.setAttribute("suspendDate", this.dateUtil().changeDateFromDB(bean.getSuspendDate(), "/"));
						count++;
					}
				}*/
				if (count == ls.size()) {
					isConfirm = true;
				}
				request.setAttribute("isConfirm", isConfirm);
				request.setAttribute("job", "edit");
			}
			request.setAttribute("hiddenMM", arrStr[1]);
			request.setAttribute("hiddenYYYY", arrStr[2]);
			request.setAttribute("listData", listData);
			request.setAttribute("areaLevelSelected", areaLevel);
			request.setAttribute("nhsoZoneSelected", nhsoZone);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("lsProvince", this.dataUtil(dao).lsProvince());
			request.setAttribute("lsNhsoZone", this.dataUtil(dao).lsNhsoZone());
			request.setAttribute("areaLevel", this.properties().areaLevel());
			
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String edit(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc67SuspendBean asb = new Acc67SuspendBean();
			String[] acc67Amount = request.getParameterValues("acc67Amount");
			String[] baacAmount = request.getParameterValues("baacAmount");
			String[] suspendAmount = request.getParameterValues("suspendAmount");
			String[] provinceId = request.getParameterValues("provinceId");
			String suspendDate = this.getAttr(request, "suspendDate");
			String mm = this.getAttr(request, "hiddenMM");
			String yyyy = this.getAttr(request, "hiddenYYYY");
			Acc67SuspendBean bean = new Acc67SuspendBean();
			
			for (int i =0 ;i < acc67Amount.length;i++) {
				asb = new Acc67SuspendBean();
				bean = new Acc67SuspendBean();
				
				bean.setProvinceId(provinceId[i]);
				bean.setMm(mm);
				bean.setYyyy(yyyy);
				dao.select(bean);
				
				asb.setAcc67Amount(acc67Amount[i].replace(",", ""));
				asb.setBaacAmount(baacAmount[i].replace(",", ""));
				asb.setSuspendAmount(suspendAmount[i].replace(",", ""));
				asb.setProvinceId(provinceId[i]);
				asb.setMm(mm);
				asb.setYyyy(yyyy);
				asb.setSuspendDate(this.dateUtil().changeDateToDB(suspendDate));
				
				if (bean.getAcc67Amount() == null || bean.getAcc67Amount().equals("")) {
					dao.insert(asb);
				} else {
					dao.update(asb);
				}
			}
			
			request.setAttribute("suspendDate", suspendDate);
			conn.commit();
		} catch (Exception e) {
			dao.printMessage();
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				this.searchByDate(request);
				this.message(request).setMsgCode("I002");
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String confirm(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc67SuspendBean asb = new Acc67SuspendBean();
			String[] acc67Amount = request.getParameterValues("acc67Amount");
			String[] baacAmount = request.getParameterValues("baacAmount");
			String[] suspendAmount = request.getParameterValues("suspendAmount");
			String[] provinceId = request.getParameterValues("provinceId");
			String suspendDate = this.getAttr(request, "suspendDate");
			String mm = this.getAttr(request, "hiddenMM");
			String yyyy = this.getAttr(request, "hiddenYYYY");
			for (int i =0 ;i < acc67Amount.length;i++) {
				asb = new Acc67SuspendBean();
				asb = new Acc67SuspendBean();
				asb.setAcc67Amount(acc67Amount[i].replace(",", ""));
				asb.setBaacAmount(baacAmount[i].replace(",", ""));
				asb.setSuspendAmount(suspendAmount[i].replace(",", ""));
				asb.setProvinceId(provinceId[i]);
				asb.setMm(mm);
				asb.setYyyy(yyyy);
				asb.setSuspendDate(this.dateUtil().changeDateToDB(suspendDate));
				asb.setSuspendStatus("Y");
				dao.update(asb);
			}
			request.setAttribute("suspendDate", suspendDate);
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				this.searchByDate(request);
				this.message(request).setMsgCode("I002");
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	
	private String save(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			Acc67SuspendBean asb = new Acc67SuspendBean();
			String[] acc67Amount = request.getParameterValues("acc67Amount");
			String[] baacAmount = request.getParameterValues("baacAmount");
			String[] suspendAmount = request.getParameterValues("suspendAmount");
			String[] provinceId = request.getParameterValues("provinceId");
			String suspendDate = this.getAttr(request, "suspendDate");
			String[] arrStr = suspendDate.split("\\/");
			for (int i =0 ;i < acc67Amount.length;i++) {
				asb = new Acc67SuspendBean();
				asb.setAcc67Amount(acc67Amount[i].replace(",", ""));
				asb.setBaacAmount(baacAmount[i].replace(",", ""));
				asb.setSuspendAmount(suspendAmount[i].replace(",", ""));
				asb.setProvinceId(provinceId[i]);
				asb.setMm(arrStr[1]);
				asb.setYyyy(arrStr[2]);
				asb.setSuspendStatus("P");
				asb.setSuspendDate(this.dateUtil().changeDateToDB(suspendDate));
				dao.insert(asb);
			}
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				this.searchByDate(request);
				this.message(request).setMsgCode("I002");
				this.url = "/apps/acc6/ACC60108A.jsp";
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String export(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);

			String suspendDate = this.getAttr(request, "suspendDate");
			String[] arrStr  = suspendDate.split("\\/");
			String mm = this.getAttr(request, "mm");
			String yyyy = this.getAttr(request, "yyyy");
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\""+arrStr[0]+mm+yyyy+".xls\"");
			response.setCharacterEncoding("windows-874");
			
			List ls = new ArrayList();
			Acc67SuspendBean asb = new Acc67SuspendBean();
			asb.setMm(mm);
			asb.setYyyy(yyyy);
			
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("กระทบยอดเงินฝากธนาคาร ยอดเงิน ณ วันที่ "+arrStr[0]+"/"+mm+"/"+yyyy);
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFRow row = sheet.createRow((short)0);
			
			ls = dao.selectList(asb, " ORDER BY PROVINCE_ID ASC");
			if (ls.size() > 0) {
				
				row.setRowStyle(cellStyle);
				sheet.setDefaultColumnWidth((short) 30);
				row.createCell((short)0).setCellStyle(cellStyle);
				row.createCell((short)1).setCellStyle(cellStyle);
				row.createCell((short)2).setCellStyle(cellStyle);
				row.createCell((short)3).setCellStyle(cellStyle);
				row.createCell((short)4).setCellStyle(cellStyle);

				row.createCell((short)0).setCellValue("รหัสจังหวัด");
				row.createCell((short)1).setCellValue("จังหวัด");
				row.createCell((short)2).setCellValue("ยอดเงินในระบบ");
				row.createCell((short)3).setCellValue("ยอดเงินฝากธนาคาร");
				row.createCell((short)4).setCellValue("กองทุนย่อยเพื่อรอการตวรจสอบ");
				
				for (int i = 0 ; i < ls.size() ; i++ ) {
					row = sheet.createRow((short)(i+1));
					Acc67SuspendBean bean = (Acc67SuspendBean) ls.get(i);
					row.createCell((short)0).setCellValue(bean.getProvinceId());
					row.createCell((short)1).setCellValue(this.dataUtil(dao).getProvinceName(bean.getProvinceId()));
					row.createCell((short)2).setCellValue(bean.getAcc67Amount());
					row.createCell((short)3).setCellValue(bean.getBaacAmount());
					row.createCell((short)4).setCellValue(bean.getSuspendAmount());
				}
				
			}
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}
	private String search(HttpServletRequest request){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			String yyyy = this.getAttr(request, "yyyy");
			String mm = this.getAttr(request, "mm");
			List ls = new ArrayList();
			List listData = new ArrayList();
			String areaLevel = this.getAttr(request, "areaLevel");
			String nhsoZone = this.getAttr(request, "nhsoZone");
			String provinceId = this.getAttr(request, "province");
			int count = 0;
			boolean isConfirm = false;
			String result = "";
			Map map = new HashMap();
			StringBuffer sb = new StringBuffer();
			double expenseAmt = 0;
			double incomeAnt = 0;
			ls = this.dataUtil(dao).lsProvince();
			Acc67SuspendBean asb = new Acc67SuspendBean();
			if (!areaLevel.equals("1")) {
				if (areaLevel.equals("2")) {
					ls = this.dataUtil(dao).lsProvinceByZone(nhsoZone);
				} else if (areaLevel.equals("3")) {
					ls = this.dataUtil(dao).lsProvinceById(provinceId);
				}
			}
			
			for (int i=0;i<ls.size();i++) {
				map = (Map)ls.get(i);
				asb = new Acc67SuspendBean();
				asb.setProvinceId(dao.chkData(map.get("PROVINCE_ID")));
				asb.setMm(mm);
				asb.setYyyy(yyyy);
				dao.select(asb);
				if (asb.getAcc67Amount() == null || asb.getAcc67Amount().equals("")) {
					sb.delete(0, sb.length());
					sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) AS SUM_ACC6_AMOUNT ");
					sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
					sb.append("AND INCOME_DATE <= TO_DATE('"+mm+yyyy+"235959','MMYYYYHH24MISS') ");
					incomeAnt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
					sb.delete(0, sb.length());
					sb.append("SELECT (NVL(SUM(EXPENSE_AMOUNT), 0)+NVL(SUM(TAX_VALUE), 0)) AS SUM_ACC6_AMOUNT ");
					sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
					sb.append("AND EXPENSE_DATE  <= TO_DATE('"+mm+yyyy+"235959','MMYYYYHH24MISS') ");
					expenseAmt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
					result = String.valueOf(incomeAnt - expenseAmt);
					asb.setProvinceId(map.get("PROVINCE_ID").toString());
					asb.setProvinceName(map.get("PROVINCE_NAME").toString());
					asb.setAcc67Amount(result);
					listData.add(asb);
				} else {
					asb.setProvinceName(map.get("PROVINCE_NAME").toString());
					if (asb.getSuspendStatus().equals("Y")) {
						request.setAttribute("suspendDate", this.dateUtil().changeDateFromDB(asb.getSuspendDate(), "/"));
						count++;
					}
					listData.add(asb);
				}
			}
			
			if (count == ls.size()) {
				isConfirm = true;
				request.setAttribute("isConfirm", isConfirm);
			}
			if (ls.size() <= 0) {
				this.message(request).setMsgCode("I001");
			}
			request.setAttribute("yyyy", yyyy);
			request.setAttribute("mm", mm);
			request.setAttribute("hiddenMM", mm);
			request.setAttribute("hiddenYYYY", yyyy);
			request.setAttribute("listData", listData);
			request.setAttribute("job", "search");
			request.setAttribute("listBudgetYear", this.dataUtil(dao).lsBudgetYear());
			request.setAttribute("areaLevelSelected", areaLevel);
			request.setAttribute("nhsoZoneSelected", nhsoZone);
			request.setAttribute("provinceId", provinceId);
			request.setAttribute("job", "serach");
			request.setAttribute("lsProvince", this.dataUtil(dao).lsProvince());
			request.setAttribute("lsNhsoZone", this.dataUtil(dao).lsNhsoZone());
			request.setAttribute("areaLevel", this.properties().areaLevel());
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao = null;
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String downloadTemplate(HttpServletRequest request, HttpServletResponse response){
		Connection conn = null;
		ISUD dao = null;
		try {
			conn = DBConnection.getConnection();
			dao = ISUDFactory.create(conn);
			double expenseAmt = 0;
			double incomeAnt = 0;
			String result = "";
			String suspendDate = this.getAttr(request, "suspendDate");
			StringBuffer sb = new StringBuffer();
			response.setContentType("text/vnd.ms-excel");
			response.setHeader("Content-Disposition", "attachment; filename=\"Template.xls\"");
			response.setCharacterEncoding("windows-874");
			
			List listProvince = new ArrayList();
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet("Template");
			HSSFCellStyle cellStyle = wb.createCellStyle();
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			HSSFRow row = sheet.createRow((short)0);
			listProvince = this.dataUtil(dao).lsProvince();
			if (listProvince.size() > 0) {
				row.setRowStyle(cellStyle);
				sheet.setDefaultColumnWidth((short) 30);
				row.createCell((short)0).setCellStyle(cellStyle);
				row.createCell((short)1).setCellStyle(cellStyle);
				row.createCell((short)2).setCellStyle(cellStyle);
				row.createCell((short)3).setCellStyle(cellStyle);

				row.createCell((short)0).setCellValue("รหัสจังหวัด");
				row.createCell((short)1).setCellValue("จังหวัด");
				row.createCell((short)2).setCellValue("ยอดเงินในระบบ");
				row.createCell((short)3).setCellValue("ยอดเงินฝากธนาคาร");
				
				for (int i = 0 ; i < listProvince.size() ; i++ ) {
					row = sheet.createRow((short)(i+1));
					Map map = (Map) listProvince.get(i);
					sb.delete(0, sb.length());
					sb.append("SELECT NVL(SUM(INCOME_AMOUNT), 0) AS SUM_ACC6_AMOUNT ");
					sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
					sb.append("AND INCOME_DATE <= TO_DATE('"+this.dateUtil().changeFormatDate(suspendDate, "/","")+"235959','DDMMYYYYHH24MISS') ");
					incomeAnt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
					sb.delete(0, sb.length());
					sb.append("SELECT (NVL(SUM(EXPENSE_AMOUNT), 0)+NVL(SUM(TAX_VALUE), 0)) AS SUM_ACC6_AMOUNT ");
					sb.append("FROM "+this.schema+".ACC6_TRANSACTION ");
					sb.append("WHERE PROVINCE_ID  = '"+map.get("PROVINCE_ID").toString()+"' AND REASON_CODE = '00' ");
					sb.append("AND EXPENSE_DATE  <= TO_DATE('"+this.dateUtil().changeFormatDate(suspendDate, "/","")+"235959','DDMMYYYYHH24MISS') ");
					expenseAmt = Double.parseDouble(dao.findDataSql(sb.toString()).get("SUM_ACC6_AMOUNT").toString());
					result = String.valueOf(incomeAnt - expenseAmt);
					row.createCell((short)0).setCellValue(dao.chkData(map.get("PROVINCE_ID")));
					row.createCell((short)1).setCellValue(dao.chkData(map.get("PROVINCE_NAME")));
					row.createCell((short)2).setCellValue(this.digitUtil().formatDecimal(Double.parseDouble(dao.chkData(result)), 2));
				}
				
			}
			OutputStream out = response.getOutputStream();
			wb.write(out);
			out.close();
			conn.commit();
		} catch (Exception e) {
			try {
				if (conn != null) {
					conn.rollback();
				}
			} catch (SQLException eSQL) {
				eSQL.printStackTrace();
			}
			this.message(request).setMsg("E", e.toString() + ":" + dao.getMessage(), e.getStackTrace());
		} finally {
			try {
				if (conn != null) {
					conn.close();
				}
				dao.printMessage();
				dao = null;
				this.url = "";
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return this.url;
	}

	private String upload(HttpServletRequest request){
		this.url = "/apps/acc6/ACC60108C.jsp";
		return this.url;
	}
}
